/*
 * TaskDatabaseHelper.java
 *
 * Copyright (C) 2012 Marten Gajda <marten@dmfs.org>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published
 * by the Free Software Foundation; either version 2 of the License,
 * or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
 * USA
 */

package ms.jung.andorid.caldavtodo;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Task database helper takes case of creating and updating the task database, including tables, indices and triggers.
 * 
 * TODO: create indices!
 * 
 * @author Marten Gajda <marten@dmfs.org>
 * 
 */
public class TaskDatabaseHelper extends SQLiteOpenHelper {

    private static final String TAG = "TaskDatabaseHelper";

    private static final boolean LOGD = false;

    private static final String DATABASE_NAME = "tasks.db";

    static final int DATABASE_VERSION = 1;

    public interface Tables {

	public static final String LISTS = "Lists";

	public static final String TASKS = "Tasks";

	public static final String CATEGORIES = "Categories";
    }

    TaskDatabaseHelper(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
	db.execSQL("CREATE TABLE " + Tables.LISTS + " ( "
		+ TaskContract.TaskLists._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
		+ TaskContract.TaskLists.ACCOUNT_NAME + " TEXT,"
		+ TaskContract.TaskLists.ACCOUNT_TYPE + " TEXT,"
		+ TaskContract.TaskLists.NAME + " TEXT,"
		+ TaskContract.TaskLists.COLOR + " INTEGER,"
		+ TaskContract.TaskLists.ACCESS_LEVEL + " INTEGER,"
		+ TaskContract.TaskLists.VISIBLE + " INTEGER,"
		+ TaskContract.TaskLists.SYNC_ENABLED + " INTEGER,"
		+ TaskContract.TaskLists.OWNER + " TEXT,"
		+ TaskContract.TaskLists._DIRTY + " INTEGER DEFAULT 0,"
		+ TaskContract.TaskLists.SYNC_SOURCE + " TEXT,"
		+ TaskContract.TaskLists.SYNC_VERSION + " TEXT,"
		+ TaskContract.TaskLists.SYNC1 + " TEXT,"
		+ TaskContract.TaskLists.SYNC2 + " TEXT,"
		+ TaskContract.TaskLists.SYNC3 + " TEXT,"
		+ TaskContract.TaskLists.SYNC4 + " TEXT,"
		+ TaskContract.TaskLists.SYNC5 + " TEXT,"
		+ TaskContract.TaskLists.SYNC6 + " TEXT,"
		+ TaskContract.TaskLists.SYNC7 + " TEXT,"
		+ TaskContract.TaskLists.SYNC8 + " TEXT);");

	// trigger that removes tasks of a list that has been removed
	db.execSQL("CREATE TRIGGER task_list_cleanup_trigger AFTER DELETE ON " + Tables.LISTS + " BEGIN DELETE FROM "
		+ Tables.TASKS + " WHERE " + TaskContract.Tasks.LIST_ID + "= old." + TaskContract.TaskLists._ID
		+ "; END");

	db.execSQL("CREATE TABLE " + Tables.TASKS + " ( "
		+ TaskContract.Tasks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
		+ TaskContract.Tasks.LIST_ID + " INTEGER,"
		+ TaskContract.Tasks.ACCOUNT_NAME + " TEXT,"
		+ TaskContract.Tasks.ACCOUNT_TYPE + " TEXT,"
		+ TaskContract.Tasks.TITLE + " TEXT,"
		+ TaskContract.Tasks.LOCATION + " TEXT,"
		+ TaskContract.Tasks.DESCRIPTION + " TEXT,"
		+ TaskContract.Tasks.COMMENT + " TEXT,"
		+ TaskContract.Tasks.PRIORITY + " INTEGER DEFAULT " + TaskContract.Tasks.PRIORITY_DEFAULT  + ","
		+ TaskContract.Tasks.COLOR + " INTEGER,"
		+ TaskContract.Tasks.CLASSIFICATION + " INTEGER DEFAULT " + TaskContract.Tasks.CLASSIFICATION_DEFAULT  + ","
		+ TaskContract.Tasks.COMPLETED + " INTEGER,"
		+ TaskContract.Tasks.PERCENT_COMPLETE + " INTEGER,"
		+ TaskContract.Tasks.STATUS + " INTEGER DEFAULT " + TaskContract.Tasks.PRIORITY_DEFAULT  + ","
		+ TaskContract.Tasks.DTSTART + " INTEGER,"
		+ TaskContract.Tasks.TZ + " TEXT,"
		+ TaskContract.Tasks.DUE + " INTEGER,"
		+ TaskContract.Tasks.DURATION + " TEXT,"
		+ TaskContract.Tasks.EXDATES + " TEXT,"
		+ TaskContract.Tasks.EXRULE + " TEXT,"
		+ TaskContract.Tasks.ORIGINAL_INSTANCE + " INTEGER,"
		+ TaskContract.Tasks.ORIGINAL_INSTANCE_TIME + " INTEGER,"
		+ TaskContract.Tasks._DIRTY + " INTEGER DEFAULT 1," // a new task is always dirty
		+ TaskContract.Tasks._DELETED + " INTEGER DEFAULT 0,"
		+ TaskContract.Tasks.SYNC_SOURCE + " TEXT,"
		+ TaskContract.Tasks.SYNC_VERSION + " TEXT,"
		+ TaskContract.Tasks.SYNC1 + " TEXT,"
		+ TaskContract.Tasks.SYNC2 + " TEXT,"
		+ TaskContract.Tasks.SYNC3 + " TEXT,"
		+ TaskContract.Tasks.SYNC4 + " TEXT,"
		+ TaskContract.Tasks.SYNC5 + " TEXT,"
		+ TaskContract.Tasks.SYNC6 + " TEXT,"
		+ TaskContract.Tasks.SYNC7 + " TEXT,"
		+ TaskContract.Tasks.SYNC8 + " TEXT);"
	);

	// trigger that marks a list as dirty if a task in that list gets marked as dirty or deleted
	db.execSQL("CREATE TRIGGER task_list_make_dirty_on_update AFTER UPDATE ON " + Tables.TASKS + " BEGIN UPDATE "
		+ Tables.LISTS + " SET " + TaskContract.TaskLists._DIRTY + "=" + TaskContract.TaskLists._DIRTY + " + "
		+ "new." + TaskContract.Tasks._DIRTY + " + " + "new." + TaskContract.Tasks._DELETED + " WHERE "
		+ TaskContract.TaskLists._ID + "= new." + TaskContract.Tasks.LIST_ID + "; END");

	// trigger that marks a list as dirty if a task in that list gets marked as dirty or deleted
	db.execSQL("CREATE TRIGGER task_list_make_dirty_on_insert AFTER INSERT ON " + Tables.TASKS + " BEGIN UPDATE "
		+ Tables.LISTS + " SET " + TaskContract.TaskLists._DIRTY + "=" + TaskContract.TaskLists._DIRTY + " + "
		+ "new." + TaskContract.Tasks._DIRTY + " + " + "new." + TaskContract.Tasks._DELETED + " WHERE "
		+ TaskContract.TaskLists._ID + "= new." + TaskContract.Tasks.LIST_ID + "; END");

	db.execSQL("CREATE TABLE " + Tables.CATEGORIES
		+ " ( " + TaskContract.Categories._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
		+ TaskContract.Categories.ACCOUNT_NAME + " TEXT,"
		+ TaskContract.Categories.ACCOUNT_TYPE + " TEXT,"
		+ TaskContract.Categories.NAME + " TEXT,"
		+ TaskContract.Categories.COLOR + " INTEGER);");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	// for now just drop all data and recreate tables

	db.execSQL("DROP TABLE IF EXISTS " + Tables.LISTS);
	db.execSQL("DROP TABLE IF EXISTS " + Tables.TASKS);
	db.execSQL("DROP TABLE IF EXISTS " + Tables.CATEGORIES);
	db.execSQL("DROP TRIGGER IF EXISTS task_list_make_dirty_on_insert");
	db.execSQL("DROP TRIGGER IF EXISTS task_list_make_dirty_on_update");
	db.execSQL("DROP TRIGGER IF EXISTS task_list_cleanup_trigger");
	onCreate(db);
    }

}
